﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class OrderDAL : IOrderDAL
    {
        #region SQL
        public const string sqlGetOrders = "SELECT TbOrder.*, TbProduct.ProductName FROM TbOrder LEFT OUTER JOIN TbProduct ON TbOrder.ProductID = TbProduct.ProductID";
        public const string sqlGetOrderByOrderID = "SELECT TbOrder.*, TbProduct.ProductName FROM TbOrder LEFT OUTER JOIN TbProduct ON TbOrder.ProductID = TbProduct.ProductID WHERE (OrderID = {0})";
        public const string sqlGetTop10 = "SELECT SUM(TbOrder.Count) AS Count, TbOrder.ProductID, (SELECT ProductName FROM TbProduct WHERE (ProductID = TbOrder.ProductID)) AS ProductName FROM TbOrder LEFT OUTER JOIN TbProduct AS TbProduct_1 ON TbOrder.ProductID = TbProduct_1.ProductID GROUP BY TbOrder.ProductID";
        const string sqlInsertOrder = "INSERT INTO TbOrder (OrderID, ProductID, Count, Phone,QQ,Email,Address,Memo,Sizes,Colors,Price,Date) VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})";
        const string sqlDeleteOrder = "Delete from TbOrder where OrderID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetOrders()
        {
            return DAO.Select(sqlGetOrders);
        }

        public System.Data.DataTable GetOrderByOrderID(string orderID)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@OrderID", orderID)
            };
            string sql = string.Format(sqlGetOrderByOrderID, "@OrderID");
            return DAO.Select(sql, sp);
        }

        public bool InsertOrder(Model.Order order)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@OrderID", order.OrderID),
                new SqlParameter ("@ProductID", order.ProductID),
                new SqlParameter ("@Count", order.Count),
                new SqlParameter ("@Phone", order.Phone),
                new SqlParameter ("@QQ", order.QQ),
                new SqlParameter ("@Email", order.Email),
                new SqlParameter ("@Address", order.Address),
                new SqlParameter ("@Memo", order.Memo),
                new SqlParameter ("@Sizes", order.Sizes),
                new SqlParameter ("@Colors", order.Colors),
                new SqlParameter ("@Price", order.Price),
                new SqlParameter ("@Date", order.Date)
            };
            string sql = string.Format(sqlInsertOrder, "@OrderID", "@ProductID", "@Count", "@Phone", "@QQ", "@Email", "@Address", "@Memo", "@Sizes", "@Colors", "@Price", "@Date");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteOrder(string orderID)
        {
            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@OrderID",orderID),
            };
            string sql = string.Format(sqlDeleteOrder, "@OrderID");
            return DAO.ExecuteSql(sql, sp);
        }
    }
}
